# -*- coding: utf-8 -*-
# coding=utf-8
'''
@author: Administrator
'''

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

import os
import codecs
from openpyxl.reader.excel import load_workbook


"""
    解析excel文件
"""
def analysisile():
    pass


"""
    解析excel文件，基于openpyxl1.5.0版本解析
    适配sql server数据库语法
"""
def analysisFileOpenpyxl15():
    xlsx_file = 'resour1ce.xlsx'
    
    wb = load_workbook(filename=xlsx_file)

    sheet_names = wb.get_sheet_names() 
    #生成的sql语句
    sql_str = []
    for sheet_name in sheet_names:
        sheet = wb.get_sheet_by_name(sheet_name)

        sql_table_name = sheet.cell('D4').value  
        if not sql_table_name:
            continue
        
        sql_str.append('''
        DROP TABLE IF EXISTS `''' + sql_table_name + '''`;
        ''')
        sql_str.append('CREATE TABLE `' + sql_table_name + '` (')
        
        #生成字段(第一行字段默认为主键)
        max_row = sheet.get_highest_row()
        for sheet_row in range(0, max_row):

            if sheet_row < 8:
                continue

            if not sheet.cell(row=sheet_row, column=2).value:
                break
            
            print sheet.cell(row=sheet_row, column=2).value
            
            row_sql_str = ("`" + str(sheet.cell(row=sheet_row, column=2).value) 
                           + "` " + str(sheet.cell(row=sheet_row, column=4).value))
            
            if sheet.cell(row=sheet_row, column=5).value == "Y":
                row_sql_str = row_sql_str + " NOT NULL"
            else:
                row_sql_str = row_sql_str + " DEFAULT NULL"
                
            #抽取注释
            comment = ''
            if sheet.cell(row=sheet_row, column=2).value:
                
                comment = str(sheet.cell(row=sheet_row, column=2).value)
            if sheet.cell(row=sheet_row, column=7).value:
                if comment:
                    comment = comment + "；" + str(sheet.cell(row=sheet_row, column=7).value)
                else:
                    comment = comment + str(sheet.cell(row=sheet_row, column=7).value)
            
            '''sql server不加字段描述'''
            #row_sql_str = row_sql_str + " COMMENT '" + comment + "'"
            
            
            
            sql_str.append(row_sql_str + ",")
        
        sql_str.append("PRIMARY KEY (`" + sheet.cell(row=8, column=2).value + "`)")
        sql_str.append(");")
        sql_str.append("")
        
    #写入sql文件
    writer_file_path = os.path.join(os.getcwd(), 'm_table.sql')
    write_file = codecs.open(writer_file_path, 'a', "utf-8")
    write_file.writelines('\r\n'.join(sql_str))


"""
    生成sql语句
"""
def generateql():
    pass


def Execute():
    
    
    # 1. 得到所有的xlsx文件
    all_xlsx_files = []
    for file in os.listdir(os.getcwd()):
        if os.path.isdir(file):
            continue
        
        file_name =  file.split('.')[0]
        file_ext = file.split('.')[1] 
        
        if file_ext == 'xlsx':
            all_xlsx_files
    

def AnalysisFile():
    xlsx_file = 'order.xlsx'
    
    wb = load_workbook(filename=xlsx_file)

    print wb.get_sheet_names()

    sheet_names = wb.get_sheet_names() 
    for sheet_name in sheet_names:
        ws = wb.get_sheet_by_name(sheet_name)
        print ws.cell('D4').value  

        

    return

    #生成的sql语句
    sql_str = []
#     print wb
    for sheet in wb:
        sql_table_name = sheet['D4'].value
        if not sql_table_name:
            continue
        
        sql_str.append('DROP TABLE IF EXISTS `' + sql_table_name + '`;')
        sql_str.append('CREATE TABLE `' + sql_table_name + '` (')
        
        #生成字段(第一行字段默认为主键)
        max_row = sheet.get_highest_row()
        for sheet_row in range(0, max_row):
            if sheet_row < 8:
                continue
            
            if not sheet.cell(row=sheet_row, column=2).value:
                break
            
            row_sql_str = ("`" + str(sheet.cell(row=sheet_row, column=2).value) 
                           + "` " + str(sheet.cell(row=sheet_row, column=4).value))
            
            if sheet.cell(row=sheet_row, column=5).value == "Y":
                row_sql_str = row_sql_str + " NOT NULL"
            else:
                row_sql_str = row_sql_str + " DEFAULT NULL"
                
            #抽取注释
            comment = ''
            if sheet.cell(row=sheet_row, column=3).value:
                
                comment = str(sheet.cell(row=sheet_row, column=3).value)
            if sheet.cell(row=sheet_row, column=8).value:
                if comment:
                    comment = comment + "；" + str(sheet.cell(row=sheet_row, column=8).value)
                else:
                    comment = comment + str(sheet.cell(row=sheet_row, column=8).value)
            
            row_sql_str = row_sql_str + " COMMENT '" + comment + "'"
            
            
            
            sql_str.append(row_sql_str + ",")
        
        sql_str.append("PRIMARY KEY (`" + sheet.cell(row=8, column=2).value + "`)")
        sql_str.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;")
        sql_str.append("")
        
    #写入sql文件
    writer_file_path = os.path.join(os.getcwd(), 'm_table.sql')
    write_file = codecs.open(writer_file_path, 'a', "utf-8")
    write_file.writelines('\r\n'.join(sql_str))
    
analysisFileOpenpyxl15()
print 'success'

